/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     16/12/2011 12:37:26 p.m.                     */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ACTIVIDADES_PERIODO') and o.name = 'FK_ACTIVIDA_DEFINE_LA_DEF_EVA_')
alter table ADMININCA.ACTIVIDADES_PERIODO
   drop constraint FK_ACTIVIDA_DEFINE_LA_DEF_EVA_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ACTIVIDADES_PERIODO') and o.name = 'FK_ACTIVIDA_LAS_ACTIV_ACTIVIDA')
alter table ADMININCA.ACTIVIDADES_PERIODO
   drop constraint FK_ACTIVIDA_LAS_ACTIV_ACTIVIDA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ALUMNOS') and o.name = 'FK_ALUMNOS_CADA_ALUM_USUARIOS')
alter table ADMININCA.ALUMNOS
   drop constraint FK_ALUMNOS_CADA_ALUM_USUARIOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ALUMNOS_GRADOS') and o.name = 'FK_ALUMNOS__ALUMNOS_P_GRADOS')
alter table ADMININCA.ALUMNOS_GRADOS
   drop constraint FK_ALUMNOS__ALUMNOS_P_GRADOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ALUMNOS_GRADOS') and o.name = 'FK_ALUMNOS__ALUMNOS_Q_ALUMNOS')
alter table ADMININCA.ALUMNOS_GRADOS
   drop constraint FK_ALUMNOS__ALUMNOS_Q_ALUMNOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.ASISTENCIAS') and o.name = 'FK_ASISTENC_ASISTENCI_ALUMNOS')
alter table ADMININCA.ASISTENCIAS
   drop constraint FK_ASISTENC_ASISTENCI_ALUMNOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.DEF_EVA_PERIODOS') and o.name = 'FK_DEF_EVA__DEFINE_EL_PERIODOS')
alter table ADMININCA.DEF_EVA_PERIODOS
   drop constraint FK_DEF_EVA__DEFINE_EL_PERIODOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.DEF_EVA_PERIODOS') and o.name = 'FK_DEF_EVA__ESPECIFIC_GRADOS_P')
alter table ADMININCA.DEF_EVA_PERIODOS
   drop constraint FK_DEF_EVA__ESPECIFIC_GRADOS_P
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.FUNCIONES_ROLES') and o.name = 'FK_FUNCIONE_DEFINICIO_ROLES')
alter table ADMININCA.FUNCIONES_ROLES
   drop constraint FK_FUNCIONE_DEFINICIO_ROLES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.FUNCIONES_ROLES') and o.name = 'FK_FUNCIONE_FUNCIONES_FUNCIONE')
alter table ADMININCA.FUNCIONES_ROLES
   drop constraint FK_FUNCIONE_FUNCIONES_FUNCIONE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA') and o.name = 'FK_GRADOS_P_GRADO_QUE_GRADOS')
alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   drop constraint FK_GRADOS_P_GRADO_QUE_GRADOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA') and o.name = 'FK_GRADOS_P_MATERIAS__MATERIAS')
alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   drop constraint FK_GRADOS_P_MATERIAS__MATERIAS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA') and o.name = 'FK_GRADOS_P_PROFESOR__PROFESOR')
alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   drop constraint FK_GRADOS_P_PROFESOR__PROFESOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.NOTAS_ALUMNOS') and o.name = 'FK_NOTAS_AL_ALUMNO_AL_ALUMNOS')
alter table ADMININCA.NOTAS_ALUMNOS
   drop constraint FK_NOTAS_AL_ALUMNO_AL_ALUMNOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.NOTAS_ALUMNOS') and o.name = 'FK_NOTAS_AL_NOTAS_ALU_ACTIVIDA')
alter table ADMININCA.NOTAS_ALUMNOS
   drop constraint FK_NOTAS_AL_NOTAS_ALU_ACTIVIDA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.PROFESORES') and o.name = 'FK_PROFESOR_CADA_PROF_USUARIOS')
alter table ADMININCA.PROFESORES
   drop constraint FK_PROFESOR_CADA_PROF_USUARIOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.RESPONSABLES') and o.name = 'FK_RESPONSA_UN_RESPON_USUARIOS')
alter table ADMININCA.RESPONSABLES
   drop constraint FK_RESPONSA_UN_RESPON_USUARIOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.RESPONSABLES_ALUMNO') and o.name = 'FK_RESPONSA_ALUMNO_DE_ALUMNOS')
alter table ADMININCA.RESPONSABLES_ALUMNO
   drop constraint FK_RESPONSA_ALUMNO_DE_ALUMNOS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.RESPONSABLES_ALUMNO') and o.name = 'FK_RESPONSA_CADA_ALUM_RESPONSA')
alter table ADMININCA.RESPONSABLES_ALUMNO
   drop constraint FK_RESPONSA_CADA_ALUM_RESPONSA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ADMININCA.USUARIOS') and o.name = 'FK_USUARIOS_CADA_USUA_ROLES')
alter table ADMININCA.USUARIOS
   drop constraint FK_USUARIOS_CADA_USUA_ROLES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.ACTIVIDADES_PERIODO')
            and   name  = 'LAS_ACTIVIDADES_POR_DEFECTO_DEFINEN_EL_TIPO_DE_ACTIVIDAD_CREADA_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.ACTIVIDADES_PERIODO.LAS_ACTIVIDADES_POR_DEFECTO_DEFINEN_EL_TIPO_DE_ACTIVIDAD_CREADA_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.ACTIVIDADES_PERIODO')
            and   name  = 'DEFINE_LAS_ACTIVIDADES_COMPRENDIDAS_EN_CADA_PERIODO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.ACTIVIDADES_PERIODO.DEFINE_LAS_ACTIVIDADES_COMPRENDIDAS_EN_CADA_PERIODO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ACTIVIDADES_PERIODO')
            and   type = 'U')
   drop table ADMININCA.ACTIVIDADES_PERIODO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ACTIVIDADES_X_DEFAULT')
            and   type = 'U')
   drop table ADMININCA.ACTIVIDADES_X_DEFAULT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ALUMNOS')
            and   type = 'U')
   drop table ADMININCA.ALUMNOS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.ALUMNOS_GRADOS')
            and   name  = 'ALUMNOS_QUE_PERTENECEN_AL_GRADO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.ALUMNOS_GRADOS.ALUMNOS_QUE_PERTENECEN_AL_GRADO_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.ALUMNOS_GRADOS')
            and   name  = 'ALUMNOS_PERTENECIENTES_AL_GRADO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.ALUMNOS_GRADOS.ALUMNOS_PERTENECIENTES_AL_GRADO2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ALUMNOS_GRADOS')
            and   type = 'U')
   drop table ADMININCA.ALUMNOS_GRADOS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.ASISTENCIAS')
            and   name  = 'ASISTENCIAS_DIARIAS_QUE_POSEA_CADA_ALUMNO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.ASISTENCIAS.ASISTENCIAS_DIARIAS_QUE_POSEA_CADA_ALUMNO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ASISTENCIAS')
            and   type = 'U')
   drop table ADMININCA.ASISTENCIAS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.DEF_EVA_PERIODOS')
            and   name  = 'DEFINE_EL_PERIODO_EN_EL_QUE_SE_ENCUENTRA_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.DEF_EVA_PERIODOS.DEFINE_EL_PERIODO_EN_EL_QUE_SE_ENCUENTRA_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.DEF_EVA_PERIODOS')
            and   name  = 'ESPECIFICA_GRADO_Y_MATERIA_CON_PROFESOR_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.DEF_EVA_PERIODOS.ESPECIFICA_GRADO_Y_MATERIA_CON_PROFESOR_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.DEF_EVA_PERIODOS')
            and   type = 'U')
   drop table ADMININCA.DEF_EVA_PERIODOS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.FUNCIONES')
            and   type = 'U')
   drop table ADMININCA.FUNCIONES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.FUNCIONES_ROLES')
            and   name  = 'FUNCIONES_PARA_CADA_ROL_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.FUNCIONES_ROLES.FUNCIONES_PARA_CADA_ROL_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.FUNCIONES_ROLES')
            and   name  = 'DEFINICION_DE_LAS_FUNCIONES_DEL_ROL2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.FUNCIONES_ROLES.DEFINICION_DE_LAS_FUNCIONES_DEL_ROL2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.FUNCIONES_ROLES')
            and   type = 'U')
   drop table ADMININCA.FUNCIONES_ROLES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.GRADOS')
            and   type = 'U')
   drop table ADMININCA.GRADOS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA')
            and   name  = 'GRADO_QUE_RECIBIRA_LAS_MATERIAS_IMPARTIDAS_POR_LOS_PROFESORES_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.GRADOS_PROFESOR_MATERIA.GRADO_QUE_RECIBIRA_LAS_MATERIAS_IMPARTIDAS_POR_LOS_PROFESORES_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA')
            and   name  = 'MATERIAS_QUE_RECIBIRA_EL_GRADO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.GRADOS_PROFESOR_MATERIA.MATERIAS_QUE_RECIBIRA_EL_GRADO_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA')
            and   name  = 'PROFESOR_QUE_IMPARTIRA_LA_MATERIA_EN_EL_GRADO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.GRADOS_PROFESOR_MATERIA.PROFESOR_QUE_IMPARTIRA_LA_MATERIA_EN_EL_GRADO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.GRADOS_PROFESOR_MATERIA')
            and   type = 'U')
   drop table ADMININCA.GRADOS_PROFESOR_MATERIA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.MATERIAS')
            and   type = 'U')
   drop table ADMININCA.MATERIAS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.NOTAS_ALUMNOS')
            and   name  = 'ALUMNO_AL_CUAL_PERTENECEN_LAS_NOTAS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.NOTAS_ALUMNOS.ALUMNO_AL_CUAL_PERTENECEN_LAS_NOTAS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.NOTAS_ALUMNOS')
            and   name  = 'NOTAS_ALUMNOS_EN_LA_ACTIVIDAD_DEL_PERIODO_MATERIA_PROFESOR_ANO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.NOTAS_ALUMNOS.NOTAS_ALUMNOS_EN_LA_ACTIVIDAD_DEL_PERIODO_MATERIA_PROFESOR_ANO2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.NOTAS_ALUMNOS')
            and   type = 'U')
   drop table ADMININCA.NOTAS_ALUMNOS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.PERIODOS')
            and   type = 'U')
   drop table ADMININCA.PERIODOS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.PROFESORES')
            and   type = 'U')
   drop table ADMININCA.PROFESORES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.RESPONSABLES')
            and   name  = 'UN_RESPONSABLE_PUEDE_O_NO_TENER_UN_USUARIO_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.RESPONSABLES.UN_RESPONSABLE_PUEDE_O_NO_TENER_UN_USUARIO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.RESPONSABLES')
            and   type = 'U')
   drop table ADMININCA.RESPONSABLES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.RESPONSABLES_ALUMNO')
            and   name  = 'ALUMNO_DEBE_TENER_UNO_MAS_RESPONSABLES_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.RESPONSABLES_ALUMNO.ALUMNO_DEBE_TENER_UNO_MAS_RESPONSABLES_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.RESPONSABLES_ALUMNO')
            and   name  = 'CADA_ALUMNO_DEBERA_TENER_AL_MENOS_UN_RESPONSABLE2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.RESPONSABLES_ALUMNO.CADA_ALUMNO_DEBERA_TENER_AL_MENOS_UN_RESPONSABLE2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.RESPONSABLES_ALUMNO')
            and   type = 'U')
   drop table ADMININCA.RESPONSABLES_ALUMNO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.ROLES')
            and   type = 'U')
   drop table ADMININCA.ROLES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ADMININCA.USUARIOS')
            and   name  = 'CADA_USUARIO_DEBERA_TENER_UN_SOLO_ROL_FK'
            and   indid > 0
            and   indid < 255)
   drop index ADMININCA.USUARIOS.CADA_USUARIO_DEBERA_TENER_UN_SOLO_ROL_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ADMININCA.USUARIOS')
            and   type = 'U')
   drop table ADMININCA.USUARIOS
go

if exists(select 1 from systypes where name='CORREOE')
   drop type CORREOE
go

if exists(select 1 from systypes where name='NOMBRES_APELLIDOS')
   drop type NOMBRES_APELLIDOS
go

if exists(select 1 from systypes where name='TELEFONOS')
   drop type TELEFONOS
go

/*==============================================================*/
/* Domain: CORREOE                                              */
/*==============================================================*/
create type CORREOE
   from varchar(35)
go

/*==============================================================*/
/* Domain: NOMBRES_APELLIDOS                                    */
/*==============================================================*/
create type NOMBRES_APELLIDOS
   from varchar(25)
go

/*==============================================================*/
/* Domain: TELEFONOS                                            */
/*==============================================================*/
create type TELEFONOS
   from char(9)
go

/*==============================================================*/
/* Table: ACTIVIDADES_PERIODO                                   */
/*==============================================================*/
create table ADMININCA.ACTIVIDADES_PERIODO (
   ID_ACTIVIDAD         int                  identity(1,1),
   ID_DEF_PERIODO       int                  not null,
   ID_ACTDEFAULT        char(2)              not null,
   ACTIVIDAD            varchar(20)          null,
   PORCENTAJE           int                  null
      constraint CKC_PORCENTAJE_ACTIVIDA check (PORCENTAJE is null or (PORCENTAJE between 1 and 100)),
   constraint PK_ACTIVIDADES_PERIODO primary key nonclustered (ID_ACTIVIDAD)
)
go

/*===============================================================*/
/* Index: DEFINE_LAS_ACTIVIDADES_COMPRENDIDAS_EN_CADA_PERIODO_FK */
/*===============================================================*/
create index DEFINE_LAS_ACTIVIDADES_COMPRENDIDAS_EN_CADA_PERIODO_FK on ADMININCA.ACTIVIDADES_PERIODO (
ID_DEF_PERIODO ASC
)
go

/*===========================================================================*/
/* Index: LAS_ACTIVIDADES_POR_DEFECTO_DEFINEN_EL_TIPO_DE_ACTIVIDAD_CREADA_FK */
/*===========================================================================*/
create index LAS_ACTIVIDADES_POR_DEFECTO_DEFINEN_EL_TIPO_DE_ACTIVIDAD_CREADA_FK on ADMININCA.ACTIVIDADES_PERIODO (
ID_ACTDEFAULT ASC
)
go

/*==============================================================*/
/* Table: ACTIVIDADES_X_DEFAULT                                 */
/*==============================================================*/
create table ADMININCA.ACTIVIDADES_X_DEFAULT (
   ID_ACTDEFAULT        char(2)              not null,
   NOMBRE_ACTIVIDAD     varchar(20)          null,
   PORCENT              int                  null
      constraint CKC_PORCENT_ACTIVIDA check (PORCENT is null or (PORCENT between 1 and 100)),
   ES_EDITABLE          bit                  null,
   ES_VISIBLE           bit                  null default 1,
   constraint PK_ACTIVIDADES_X_DEFAULT primary key nonclustered (ID_ACTDEFAULT)
)
go

/*==============================================================*/
/* Table: ALUMNOS                                               */
/*==============================================================*/
create table ADMININCA.ALUMNOS (
   ID_USUARIO           varchar(10)          not null,
   NOMBRES              NOMBRES_APELLIDOS    null,
   APELLIDOS            NOMBRES_APELLIDOS    null,
   DIRECCION_DE_CASA    varchar(100)         null,
   TELEFONO             TELEFONOS            null,
   constraint PK_ALUMNOS primary key nonclustered (ID_USUARIO)
)
go

/*==============================================================*/
/* Table: ALUMNOS_GRADOS                                        */
/*==============================================================*/
create table ADMININCA.ALUMNOS_GRADOS (
   ID_USUARIO           varchar(10)          not null,
   ID_GRADO             int                  not null,
   constraint PK_ALUMNOS_GRADOS primary key nonclustered (ID_USUARIO, ID_GRADO)
)
go

/*==============================================================*/
/* Index: ALUMNOS_PERTENECIENTES_AL_GRADO2_FK                   */
/*==============================================================*/
create index ALUMNOS_PERTENECIENTES_AL_GRADO2_FK on ADMININCA.ALUMNOS_GRADOS (
ID_GRADO ASC
)
go

/*==============================================================*/
/* Index: ALUMNOS_QUE_PERTENECEN_AL_GRADO_FK                    */
/*==============================================================*/
create index ALUMNOS_QUE_PERTENECEN_AL_GRADO_FK on ADMININCA.ALUMNOS_GRADOS (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Table: ASISTENCIAS                                           */
/*==============================================================*/
create table ADMININCA.ASISTENCIAS (
   ID_USUARIO           varchar(10)          not null,
   ANIO                 int                  not null,
   MES                  int                  not null,
   DIA                  int                  not null,
   HORA_ENTRADA         datetime             null,
   ENTRO                bit                  null,
   HORA_SALIDA          datetime             null,
   SALIO                bit                  null,
   constraint PK_ASISTENCIAS primary key nonclustered (ID_USUARIO, ANIO, MES, DIA)
)
go

/*==============================================================*/
/* Index: ASISTENCIAS_DIARIAS_QUE_POSEA_CADA_ALUMNO_FK          */
/*==============================================================*/
create index ASISTENCIAS_DIARIAS_QUE_POSEA_CADA_ALUMNO_FK on ADMININCA.ASISTENCIAS (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Table: DEF_EVA_PERIODOS                                      */
/*==============================================================*/
create table ADMININCA.DEF_EVA_PERIODOS (
   ID_DEF_PERIODO       int                  identity(1,1),
   ID_GRADO             int                  null,
   ID_MATERIAS          int                  null,
   ID_USUARIO           varchar(10)          null,
   ANO                  char(4)              null,
   PERIODO              char(2)              null
      constraint CKC_PERIODO_DEF_EVA_ check (PERIODO is null or (PERIODO in ('P1','P2','P3','P4'))),
   constraint PK_DEF_EVA_PERIODOS primary key nonclustered (ID_DEF_PERIODO)
)
go

/*==============================================================*/
/* Index: ESPECIFICA_GRADO_Y_MATERIA_CON_PROFESOR_FK            */
/*==============================================================*/
create index ESPECIFICA_GRADO_Y_MATERIA_CON_PROFESOR_FK on ADMININCA.DEF_EVA_PERIODOS (
ID_GRADO ASC,
ID_MATERIAS ASC,
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Index: DEFINE_EL_PERIODO_EN_EL_QUE_SE_ENCUENTRA_FK           */
/*==============================================================*/
create index DEFINE_EL_PERIODO_EN_EL_QUE_SE_ENCUENTRA_FK on ADMININCA.DEF_EVA_PERIODOS (
ANO ASC,
PERIODO ASC
)
go

/*==============================================================*/
/* Table: FUNCIONES                                             */
/*==============================================================*/
create table ADMININCA.FUNCIONES (
   ID_FUNCION           int                  identity(1,1),
   DESC_FUNCION         varchar(35)          null,
   constraint PK_FUNCIONES primary key nonclustered (ID_FUNCION)
)
go

/*==============================================================*/
/* Table: FUNCIONES_ROLES                                       */
/*==============================================================*/
create table ADMININCA.FUNCIONES_ROLES (
   ID_FUNCION           int                  not null,
   ID_ROL               char(3)              not null,
   constraint PK_FUNCIONES_ROLES primary key nonclustered (ID_FUNCION, ID_ROL)
)
go

/*==============================================================*/
/* Index: DEFINICION_DE_LAS_FUNCIONES_DEL_ROL2_FK               */
/*==============================================================*/
create index DEFINICION_DE_LAS_FUNCIONES_DEL_ROL2_FK on ADMININCA.FUNCIONES_ROLES (
ID_ROL ASC
)
go

/*==============================================================*/
/* Index: FUNCIONES_PARA_CADA_ROL_FK                            */
/*==============================================================*/
create index FUNCIONES_PARA_CADA_ROL_FK on ADMININCA.FUNCIONES_ROLES (
ID_FUNCION ASC
)
go

/*==============================================================*/
/* Table: GRADOS                                                */
/*==============================================================*/
create table ADMININCA.GRADOS (
   ID_GRADO             int                  identity(1,1),
   ANO_LECTIVO          char(4)              null,
   ANO_BACHILLERATO     char(1)              null
      constraint CKC_ANO_BACHILLERATO_GRADOS check (ANO_BACHILLERATO is null or (ANO_BACHILLERATO in ('1','2','3'))),
   ESPECIALIDAD         varchar(10)          null,
   SECCION              char(1)              null,
   constraint PK_GRADOS primary key nonclustered (ID_GRADO)
)
go

/*==============================================================*/
/* Table: GRADOS_PROFESOR_MATERIA                               */
/*==============================================================*/
create table ADMININCA.GRADOS_PROFESOR_MATERIA (
   ID_GRADO             int                  not null,
   ID_MATERIAS          int                  not null,
   ID_USUARIO           varchar(10)          not null,
   constraint PK_GRADOS_PROFESOR_MATERIA primary key nonclustered (ID_GRADO, ID_MATERIAS, ID_USUARIO)
)
go

/*==============================================================*/
/* Index: PROFESOR_QUE_IMPARTIRA_LA_MATERIA_EN_EL_GRADO_FK      */
/*==============================================================*/
create index PROFESOR_QUE_IMPARTIRA_LA_MATERIA_EN_EL_GRADO_FK on ADMININCA.GRADOS_PROFESOR_MATERIA (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Index: MATERIAS_QUE_RECIBIRA_EL_GRADO_FK                     */
/*==============================================================*/
create index MATERIAS_QUE_RECIBIRA_EL_GRADO_FK on ADMININCA.GRADOS_PROFESOR_MATERIA (
ID_MATERIAS ASC
)
go

/*=========================================================================*/
/* Index: GRADO_QUE_RECIBIRA_LAS_MATERIAS_IMPARTIDAS_POR_LOS_PROFESORES_FK */
/*=========================================================================*/
create index GRADO_QUE_RECIBIRA_LAS_MATERIAS_IMPARTIDAS_POR_LOS_PROFESORES_FK on ADMININCA.GRADOS_PROFESOR_MATERIA (
ID_GRADO ASC
)
go

/*==============================================================*/
/* Table: MATERIAS                                              */
/*==============================================================*/
create table ADMININCA.MATERIAS (
   ID_MATERIAS          int                  identity(1,1),
   DESC_FUNCION         varchar(35)          null,
   VISIBLE              bit                  null default 1,
   constraint PK_MATERIAS primary key nonclustered (ID_MATERIAS)
)
go

/*==============================================================*/
/* Table: NOTAS_ALUMNOS                                         */
/*==============================================================*/
create table ADMININCA.NOTAS_ALUMNOS (
   ID_USUARIO           varchar(10)          not null,
   ID_ACTIVIDAD         int                  not null,
   NOTA                 decimal(2,2)         null
      constraint CKC_NOTA_NOTAS_AL check (NOTA is null or (NOTA between 10.00 and 0.00)),
   constraint PK_NOTAS_ALUMNOS primary key nonclustered (ID_USUARIO, ID_ACTIVIDAD)
)
go

/*===========================================================================*/
/* Index: NOTAS_ALUMNOS_EN_LA_ACTIVIDAD_DEL_PERIODO_MATERIA_PROFESOR_ANO2_FK */
/*===========================================================================*/
create index NOTAS_ALUMNOS_EN_LA_ACTIVIDAD_DEL_PERIODO_MATERIA_PROFESOR_ANO2_FK on ADMININCA.NOTAS_ALUMNOS (
ID_ACTIVIDAD ASC
)
go

/*==============================================================*/
/* Index: ALUMNO_AL_CUAL_PERTENECEN_LAS_NOTAS_FK                */
/*==============================================================*/
create index ALUMNO_AL_CUAL_PERTENECEN_LAS_NOTAS_FK on ADMININCA.NOTAS_ALUMNOS (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Table: PERIODOS                                              */
/*==============================================================*/
create table ADMININCA.PERIODOS (
   PERIODO              char(2)              not null
      constraint CKC_PERIODO_PERIODOS check (PERIODO in ('P1','P2','P3','P4')),
   ANO                  char(4)              not null,
   CERRADO              bit                  null default 0,
   constraint PK_PERIODOS primary key nonclustered (ANO, PERIODO)
)
go

/*==============================================================*/
/* Table: PROFESORES                                            */
/*==============================================================*/
create table ADMININCA.PROFESORES (
   ID_USUARIO           varchar(10)          not null,
   NOMBRES              NOMBRES_APELLIDOS    null,
   APELLIDOS            NOMBRES_APELLIDOS    null,
   CORREO_ELECTRONICO   CORREOE              null,
   VISIBLE              bit                  null default 1,
   constraint PK_PROFESORES primary key nonclustered (ID_USUARIO)
)
go

/*==============================================================*/
/* Table: RESPONSABLES                                          */
/*==============================================================*/
create table ADMININCA.RESPONSABLES (
   ID_RESPONSABLE       int                  identity(1,1),
   ID_USUARIO           varchar(10)          null,
   NOMBRES              NOMBRES_APELLIDOS    null,
   APELLIDOS            NOMBRES_APELLIDOS    null,
   TELEFONO             TELEFONOS            null,
   CORREO_ELECTRONICO   CORREOE              null,
   constraint PK_RESPONSABLES primary key nonclustered (ID_RESPONSABLE)
)
go

/*==============================================================*/
/* Index: UN_RESPONSABLE_PUEDE_O_NO_TENER_UN_USUARIO_FK         */
/*==============================================================*/
create index UN_RESPONSABLE_PUEDE_O_NO_TENER_UN_USUARIO_FK on ADMININCA.RESPONSABLES (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Table: RESPONSABLES_ALUMNO                                   */
/*==============================================================*/
create table ADMININCA.RESPONSABLES_ALUMNO (
   ID_USUARIO           varchar(10)          not null,
   ID_RESPONSABLE       int                  not null,
   constraint PK_RESPONSABLES_ALUMNO primary key nonclustered (ID_USUARIO, ID_RESPONSABLE)
)
go

/*==============================================================*/
/* Index: CADA_ALUMNO_DEBERA_TENER_AL_MENOS_UN_RESPONSABLE2_FK  */
/*==============================================================*/
create index CADA_ALUMNO_DEBERA_TENER_AL_MENOS_UN_RESPONSABLE2_FK on ADMININCA.RESPONSABLES_ALUMNO (
ID_RESPONSABLE ASC
)
go

/*==============================================================*/
/* Index: ALUMNO_DEBE_TENER_UNO_MAS_RESPONSABLES_FK             */
/*==============================================================*/
create index ALUMNO_DEBE_TENER_UNO_MAS_RESPONSABLES_FK on ADMININCA.RESPONSABLES_ALUMNO (
ID_USUARIO ASC
)
go

/*==============================================================*/
/* Table: ROLES                                                 */
/*==============================================================*/
create table ADMININCA.ROLES (
   ID_ROL               char(3)              not null,
   DESC_ROL             varchar(20)          null,
   PASSWORD_ROL         varbinary(255)       null,
   constraint PK_ROLES primary key nonclustered (ID_ROL)
)
go

execute sys.sp_addextendedproperty 'MS_Description', 
   'Tabla que definira los roles que poseera el sistema',
   'user', 'ADMININCA', 'table', 'ROLES'
go

/*==============================================================*/
/* Table: USUARIOS                                              */
/*==============================================================*/
create table ADMININCA.USUARIOS (
   ID_USUARIO           varchar(10)          not null,
   ID_ROL               char(3)              not null,
   CONTRASENA           varbinary(255)       null,
   ACTIVADO             bit                  null,
   FECHA_CREACION       datetime             null,
   constraint PK_USUARIOS primary key nonclustered (ID_USUARIO)
)
go

/*==============================================================*/
/* Index: CADA_USUARIO_DEBERA_TENER_UN_SOLO_ROL_FK              */
/*==============================================================*/
create index CADA_USUARIO_DEBERA_TENER_UN_SOLO_ROL_FK on ADMININCA.USUARIOS (
ID_ROL ASC
)
go

alter table ADMININCA.ACTIVIDADES_PERIODO
   add constraint FK_ACTIVIDA_DEFINE_LA_DEF_EVA_ foreign key (ID_DEF_PERIODO)
      references ADMININCA.DEF_EVA_PERIODOS (ID_DEF_PERIODO)
go

alter table ADMININCA.ACTIVIDADES_PERIODO
   add constraint FK_ACTIVIDA_LAS_ACTIV_ACTIVIDA foreign key (ID_ACTDEFAULT)
      references ADMININCA.ACTIVIDADES_X_DEFAULT (ID_ACTDEFAULT)
go

alter table ADMININCA.ALUMNOS
   add constraint FK_ALUMNOS_CADA_ALUM_USUARIOS foreign key (ID_USUARIO)
      references ADMININCA.USUARIOS (ID_USUARIO)
go

alter table ADMININCA.ALUMNOS_GRADOS
   add constraint FK_ALUMNOS__ALUMNOS_P_GRADOS foreign key (ID_GRADO)
      references ADMININCA.GRADOS (ID_GRADO)
go

alter table ADMININCA.ALUMNOS_GRADOS
   add constraint FK_ALUMNOS__ALUMNOS_Q_ALUMNOS foreign key (ID_USUARIO)
      references ADMININCA.ALUMNOS (ID_USUARIO)
go

alter table ADMININCA.ASISTENCIAS
   add constraint FK_ASISTENC_ASISTENCI_ALUMNOS foreign key (ID_USUARIO)
      references ADMININCA.ALUMNOS (ID_USUARIO)
go

alter table ADMININCA.DEF_EVA_PERIODOS
   add constraint FK_DEF_EVA__DEFINE_EL_PERIODOS foreign key (ANO, PERIODO)
      references ADMININCA.PERIODOS (ANO, PERIODO)
go

alter table ADMININCA.DEF_EVA_PERIODOS
   add constraint FK_DEF_EVA__ESPECIFIC_GRADOS_P foreign key (ID_GRADO, ID_MATERIAS, ID_USUARIO)
      references ADMININCA.GRADOS_PROFESOR_MATERIA (ID_GRADO, ID_MATERIAS, ID_USUARIO)
go

alter table ADMININCA.FUNCIONES_ROLES
   add constraint FK_FUNCIONE_DEFINICIO_ROLES foreign key (ID_ROL)
      references ADMININCA.ROLES (ID_ROL)
go

alter table ADMININCA.FUNCIONES_ROLES
   add constraint FK_FUNCIONE_FUNCIONES_FUNCIONE foreign key (ID_FUNCION)
      references ADMININCA.FUNCIONES (ID_FUNCION)
go

alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   add constraint FK_GRADOS_P_GRADO_QUE_GRADOS foreign key (ID_GRADO)
      references ADMININCA.GRADOS (ID_GRADO)
go

alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   add constraint FK_GRADOS_P_MATERIAS__MATERIAS foreign key (ID_MATERIAS)
      references ADMININCA.MATERIAS (ID_MATERIAS)
go

alter table ADMININCA.GRADOS_PROFESOR_MATERIA
   add constraint FK_GRADOS_P_PROFESOR__PROFESOR foreign key (ID_USUARIO)
      references ADMININCA.PROFESORES (ID_USUARIO)
go

alter table ADMININCA.NOTAS_ALUMNOS
   add constraint FK_NOTAS_AL_ALUMNO_AL_ALUMNOS foreign key (ID_USUARIO)
      references ADMININCA.ALUMNOS (ID_USUARIO)
go

alter table ADMININCA.NOTAS_ALUMNOS
   add constraint FK_NOTAS_AL_NOTAS_ALU_ACTIVIDA foreign key (ID_ACTIVIDAD)
      references ADMININCA.ACTIVIDADES_PERIODO (ID_ACTIVIDAD)
go

alter table ADMININCA.PROFESORES
   add constraint FK_PROFESOR_CADA_PROF_USUARIOS foreign key (ID_USUARIO)
      references ADMININCA.USUARIOS (ID_USUARIO)
go

alter table ADMININCA.RESPONSABLES
   add constraint FK_RESPONSA_UN_RESPON_USUARIOS foreign key (ID_USUARIO)
      references ADMININCA.USUARIOS (ID_USUARIO)
go

alter table ADMININCA.RESPONSABLES_ALUMNO
   add constraint FK_RESPONSA_ALUMNO_DE_ALUMNOS foreign key (ID_USUARIO)
      references ADMININCA.ALUMNOS (ID_USUARIO)
go

alter table ADMININCA.RESPONSABLES_ALUMNO
   add constraint FK_RESPONSA_CADA_ALUM_RESPONSA foreign key (ID_RESPONSABLE)
      references ADMININCA.RESPONSABLES (ID_RESPONSABLE)
go

alter table ADMININCA.USUARIOS
   add constraint FK_USUARIOS_CADA_USUA_ROLES foreign key (ID_ROL)
      references ADMININCA.ROLES (ID_ROL)
go

